from datetime import datetime
import uuid

from django.db.models import F
from django.http import HttpResponse
from django.shortcuts import render
from django.views.decorators.csrf import csrf_exempt
from django.conf import settings

import pandas as pd
import openpyxl
from openpyxl.styles import Font, Alignment, colors, Color
from openpyxl.utils import get_column_letter

import oss2
# 用户账号密码，第三部说明的Access
# 阿里云主账号AccessKey拥有所有API的访问权限，风险很高。强烈建议您创建并使用RAM账号进行API访问或日常运维，请登录 https://ram.console.aliyun.com 创建RAM账号。
# 获取的AccessKey
from apps.common.account.models import Account

access_key_id = 'LTAI5tPZQKcCP1RvoCs4kgUw'
access_key_secret = 'C2xELYe9H4TJ9OBRtNT2u9PMADj4SH'
auth = oss2.Auth(access_key_id, access_key_secret)

endpoint = settings.OSS_ENDPOINT
bucket_name = 'humen-pollution'
bucket = oss2.Bucket(auth, endpoint, bucket_name)

header_font = Font(size=14, name=u'微软雅黑')
alignment = Alignment(
    horizontal='center',  # 水平对齐方式
    vertical='center',  # 垂直对齐方式
    wrap_text=True,
)


def upload_file_to_oss(file, file_name, file_path='company'):
    """
    上传文件
    :param file: b字节文件
    :param file_name: 文件名
    :param file_path: 在oss的路径
    :return: 若成功返回文件路径，若不成功返回空
    """
    # 生成外网访问的文件路径
    file_name = f"{file_path}/{file_name}".replace('+', '-')
    # 这个是阿里提供的SDK方法 bucket是调用的4.1中配置的变量名
    res = bucket.put_object(file_name, file)
    # 如果上传状态是200 代表成功 返回文件在oss中的相对路径
    # 下面代码根据自己的需求写
    if res.status == 200:
        return file_name
    else:
        return False


def download_self_ass(data):
    """ 下载企业自评 """
    tableHeader = [
        {'label': '1-1', 'key1': 'category_1', 'key2': 'category_1_1'},
        {'label': '1-2', 'key1': 'category_1', 'key2': 'category_1_2'},
        {'label': '1-3', 'key1': 'category_1', 'key2': 'category_1_3'},
        {'label': '2-1', 'key1': 'category_2', 'key2': 'category_2_1'},
        {'label': '2-2', 'key1': 'category_2', 'key2': 'category_2_2'},
        {'label': '2-3', 'key1': 'category_2', 'key2': 'category_2_3'},
        {'label': '2-4', 'key1': 'category_2', 'key2': 'category_2_4'},
        {'label': '2-5', 'key1': 'category_2', 'key2': 'category_2_5'},
        {'label': '3-1', 'key1': 'category_3', 'key2': 'category_3_1'},
        {'label': '3-2', 'key1': 'category_3', 'key2': 'category_3_2'},
        {'label': '3-3', 'key1': 'category_3', 'key2': 'category_3_3'},
        {'label': '3-4', 'key1': 'category_3', 'key2': 'category_3_4'},
        {'label': '4-1', 'key1': 'category_4', 'key2': 'category_4_1'},
        {'label': '4-2', 'key1': 'category_4', 'key2': 'category_4_2'},
        {'label': '4-3', 'key1': 'category_4', 'key2': 'category_4_3'},
        {'label': '4-4', 'key1': 'category_4', 'key2': 'category_4_4'},
        {'label': '4-5', 'key1': 'category_4', 'key2': 'category_4_5'},
        {'label': '4-6', 'key1': 'category_4', 'key2': 'category_4_6'},
        {'label': '5-1', 'key1': 'category_5', 'key2': 'category_5_1'},
        {'label': '5-2', 'key1': 'category_5', 'key2': 'category_5_2'},
        {'label': '6-1', 'key1': 'category_6', 'key2': 'category_6_1'},
        {'label': '6-2', 'key1': 'category_6', 'key2': 'category_6_2'},
        {'label': '7-1', 'key1': 'category_7', 'key2': 'category_7_1'},
        {'label': '7-2', 'key1': 'category_7', 'key2': 'category_7_2'},
        {'label': '7-3', 'key1': 'category_7', 'key2': 'category_7_3'},
        {'label': '8-1', 'key1': 'category_8', 'key2': 'category_8_1'}
    ]
    status_dict = {0: '待检查', 1: '通过', 2: '需修改'}

    wb = openpyxl.Workbook()  # 创建一个excel
    ws = wb.active
    ws.title = '企业自评'

    table_header = ['企业名称', '填写日期', '企业地址', '企业联系人', '联系人电话', '状态', '检查意见', '检查人', '检查日期',
                    '企业修改日期']
    filter_value = ['company_name', 'created_time', 'address', 'people_name', 'telephone', 'check_status',
                    'check_opinion', 'check_people_realname', 'check_time', 'change_time']
    for i in tableHeader:
        table_header.append(i['label'])
        filter_value.append(i['key2'])
    for i in data:
        i['created_time'] = i['created_time'].strftime('%Y-%m-%d %H:%M')
        i['check_time'] = i['check_time'].strftime('%Y-%m-%d %H:%M') if i['check_time'] else ''
        i['change_time'] = i['change_time'].strftime('%Y-%m-%d %H:%M') if i['change_time'] else ''
        i['check_status'] = status_dict.get(i['check_status'], '')
        for j in tableHeader:
            tmp = i[j['key1']].get(j['key2'], [])
            i[j['key2']] = '已上传' if tmp else '未上传'

    ws.append(table_header)  # 写入第一行数据
    # 对表头进行样式处理
    for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
        for cell in row:
            cell.font = header_font
            cell.alignment = alignment
    # 写入数据
    for i in data:
        row_data = []
        for key in filter_value:
            data = i.get(key, '')
            row_data.append(data)
        ws.append(row_data)

    # wb.save('./查询结果.xlsx')
    return wb


def download_answer(data):
    from apps.pollution.models import Question, Category
    answer_choice = {0: '完成', 1: '未完成', 2: '不涉及', '0': '完成', '1': '未完成', '2': '不涉及'}
    # 下载回答
    wb = openpyxl.Workbook()  # 创建一个excel
    ws = wb.active

    ws.title = f'查询结果'

    table_header = ['审核人', '审核意见', '抽查表分数', '企业名称', '企业联系人', '联系人电话', '企业地址']
    filter_value = ['handler', 'opinion', 'spot_check_score', 'company_name', 'people_name', 'telephone', 'address']
    second_header = ['', '', '', '', '', '', '']

    question = Question.objects.all().select_related('category').order_by('id').values('id', 'name', 'category__name')
    second_header.extend([i['name'] for i in question])
    filter_value.extend([i['id'] for i in question])
    table_header.extend([i['category__name'] for i in question])
    for i in data:
        answer = i['answer']
        for k, v in answer.items():
            i[int(k)] = v

    ws.append(table_header)  # 写入第一行数据
    ws.append(second_header)  # 写入第二行数据
    # 对表头进行样式处理
    for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
        for cell in row:
            cell.font = header_font
            cell.alignment = alignment
    # 合并单元格
    for i in 'ABCDEFG':
        ws.merge_cells(f"{i}1:{i}2")
    ws.merge_cells('H1:I1')
    ws.merge_cells('J1:L1')
    ws.merge_cells('M1:R1')
    ws.merge_cells('S1:X1')
    ws.merge_cells('y1:AC1')
    ws.merge_cells('y1:AD1')
    ws.merge_cells('AE1:AF1')
    ws.merge_cells('AG1:AJ1')
    ws.merge_cells('AK1:AN1')
    ws.merge_cells('AO1:AQ1')

    # 写入数据
    for i in data:
        row_data = []
        for key in filter_value:
            data = i.get(key, '')
            if isinstance(key, int):
                data = answer_choice.get(data, '')
            row_data.append(data)
        ws.append(row_data)

    return wb


def download_analysis(data):
    from apps.pollution.models import Question, Category

    queryset = Question.objects.all().order_by('sort_num').values('id', 'category_id', 'full_score',
                                                                  category_name=F('category__name'))

    ying_ji_company_name = Account.objects.filter(is_company=True, batch_num=2, ying_ji=True) \
        .values_list('username', flat=True)

    jian_kong_company_name = Account.objects.filter(is_company=True, batch_num=2, jian_kong=True) \
        .values_list('username', flat=True)

    # 获取一级指标以及子指标的id数组
    # 格式如下：
    """
     question_list = [
       {'id': 1, 'name': '指标名', 'child':[1, 2, 3]}, '''
     ]
    """

    question_list = []
    qs_dict = {'id': None, 'child': []}  # 构造对象格式
    for qs in queryset:  # 循环子题目
        if qs_dict['id'] == None:  # 如果为空，则将值写入对象
            qs_dict['id'] = qs['category_id']
            qs_dict['name'] = qs['category_name']
            small_qs = [qs['id'], qs['full_score']]
            qs_dict['child'].append(small_qs)
        else:  # 如果不为空，则判断父id是否相同
            if qs['category_id'] == qs_dict['id']:  # 相同则将子id加入
                small_qs = [qs['id'], qs['full_score']]
                qs_dict['child'].append(small_qs)
            else:  # 不相同则将对象加入数组，并重新构造对象
                question_list.append(qs_dict)
                qs_dict = {'id': None, 'child': []}
                qs_dict['id'] = qs['category_id']
                qs_dict['name'] = qs['category_name']
                small_qs = [qs['id'], qs['full_score']]
                qs_dict['child'].append(small_qs)
    question_list.append(qs_dict)


    result = []
    for answer in data:  # 循环每一家公司的答案
        company_res = []
        company_res.append(answer['company_name'])
        for question in question_list:
            qs_score = 0
            for qs in question['child']:
                if (answer['answer'][str(qs[0])] != '1'):
                    qs_score += qs[1]
            company_res.append(qs_score)

        if answer['company_name'] in ying_ji_company_name:
            company_res.append('是')
        else:
            company_res.append('否')
        if answer['company_name'] in jian_kong_company_name:
            company_res.append('是')
        else:
            company_res.append('否')
        result.append(company_res)

    # 下载回答
    wb = openpyxl.Workbook()  # 创建一个excel
    ws = wb.active

    ws.title = f'分析结果'

    table_header = ['企业名称', '一、符合项目选址及产业政策要求完成率(%)','二、废水、废气、噪声达标排放完成率(%)',
                    '三、无组织废气排放管控完成率(%)', '四、开展固体废物规范化管理完成率(%)',
                    '五、开展环境风险应急综合整治完成率(%)', '六、开展信息化管理建设完成率(%)',
                    '七、达到环境管理综合要求完成率(%)', '八、采用污染防治可行技术完成率(%)',
                    '是否涉及指标五的整治', '是否涉及指标六的整治', '第一项大指标数据是否有问题']

    ws.append(table_header)  # 写入第一行数据
    # 对表头进行样式处理
    for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
        for cell in row:
            cell.font = header_font
            cell.alignment = alignment

    for res in result:
        if res[1] != 100 and res[2] != 100:
            res[1] = res[1] + res[2]
            del res[2]
            res.append('是')
        elif res[1] == 100:
            del res[1]
            res.append('否')
        else:
            del res[2]
            res.append('否')
        try:
            for i in range(1,9):
                res[i] = str(res[i]) + '%'
            ws.append(res)
        except Exception as e:
            continue

    return wb

